package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.Bbscs_Forum;
import com.zhufei.entity.Sponsor;

public class Bbscs_ForumDao {
	private static String TABLE = "bbscs_forum";
	private static String ID = "ID";
	private static String BOARDID = "BoardID";
	private static String BOARDNAME = "BoardName";
	private static String USERNAME = "UserName";
	private static String TITLE = "Title";
	/**
	 * 获取数据库中指定版块中ParentID为空的帖子数，也就是除了评论的帖子
	 * @return  返回数据库有多少个帖子记录
	 */
	public static int getCountOfPosts(int boardID){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE+" where ParentID='' and "+BOARDID+"="+boardID+"";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count;
			}
			else 
				return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的Sponsor对象
	 * @param index 从第index条开始获取（第一条下标是0）
	 * @param number 获取Sponsor的数量，输入-1则获取数据库中所有Sponsor
	 * @param boardID 版块id
	 * @return  赞助对象的ArrayList
	 */
	public static ArrayList<Bbscs_Forum> getBbscsPosts(int index, int number, int boardID){
		ArrayList<Bbscs_Forum> postList = new ArrayList<Bbscs_Forum>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			String value = ID+","+BOARDID+","+BOARDNAME+","+USERNAME+","+TITLE;
			if(number != -1){
				sql = "select "+value+" from "+ TABLE +" where ParentID='' and "+BOARDID+"="+boardID+" limit "+index+","+number;
			}else{
				sql = "select "+value+" from " + TABLE + " where ParentID='' and "+BOARDID+"="+boardID;;
			}
			System.out.println(sql);
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				Bbscs_Forum post = new Bbscs_Forum();
				post.setUrlID(rs.getString(ID));
				post.setBoardID(rs.getString(BOARDID));
				post.setBoardName(rs.getString(BOARDNAME));
				post.setUserName(rs.getString(USERNAME));
				post.setTitle(rs.getString(TITLE));
				postList.add(post);
			}
			rs.close();
			return postList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
}
